#    Copyright 2012 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

#************************BEGIN Add Include Files to Path************************
import sys
IncludePath="/opt/coopip/djangoproject/include"
if(not IncludePath in sys.path):
    sys.path.append(IncludePath)

    
#************************END   Add Include Files to Path************************
from django.contrib.gis.geos import *
import os, time, psycopg2, datetime

#********************Setup psycopg2 and django section********************
PathDjangoRoot="/opt/coopip/"
if(not PathDjangoRoot in sys.path):
    sys.path.append(PathDjangoRoot)
from django.core.management import setup_environ
import djangoproject.settings
setup_environ(djangoproject.settings)
from djangoproject.models import *
from django.contrib.gis.geos import GEOSGeometry
import CIS, AMR, OMS, GIS
from django.contrib.gis.db import models
#********************Setup psycopg2 and django section********************
from django.db import transaction



import time


#NOTE FIXME NOTHING HAS BEEN IMPLEMENTED YET



























Instructions = """

These instructions were copied 12/06/2012.  As they are implemented in this program, the relevant portions will be copied into the code.
TWACS Database Maintenance Instructions:

Commonly needed update to fix meters missing their meter type:
	UPDATE METERACCTS SET METERTYPE='UMT_2.10' WHERE SERIALNUMBER 	
	IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=109 AND 	MODEL=2);

	COMMIT; 

Database Maintenance

Open the TNS Watchdog GUI.
Stop Command Server and wait till there are no state 3’s left in the outcmd table.
Open SQLPLUS and enter the command select state, count(*) from outcmd order by state; then hit <ENTER>
Once you verify that there are no state 3’s left stop all the services.  (This is to stop meter reads from being inserted into the database while maintenance is being performed).
Run the following commands in a sqlplus session:

DROP TABLE TEMP_DCSI_JOB_QUEUE;

CREATE TABLE TEMP_DCSI_JOB_QUEUE AS SELECT * FROM DCSI_JOB_QUEUE;

TRUNCATE TABLE DCSI_JOB_QUEUE;

INSERT INTO DCSI_JOB_QUEUE 
SELECT * FROM TEMP_DCSI_JOB_QUEUE
WHERE STATUS = 'ACTIVE'
OR STATUS = 'TEMP HOLD'
OR STATUS = 'HOLD';

COMMIT;

DROP TABLE TEMP_DCSI_JOB_INTERVALS;

CREATE TABLE TEMP_DCSI_JOB_INTERVALS AS SELECT * FROM DCSI_JOB_INTERVALS;

TRUNCATE TABLE DCSI_JOB_INTERVALS;

INSERT INTO DCSI_JOB_INTERVALS 
SELECT * FROM TEMP_DCSI_JOB_INTERVALS
WHERE JOBNO IN (
SELECT JOBNO 
FROM DCSI_JOB_QUEUE);

COMMIT;

TRUNCATE TABLE METERINGQCSTATUS;

DROP TABLE TEMP_AMRCMDLST2WAY;

CREATE TABLE TEMP_AMRCMDLST2WAY AS SELECT * FROM AMRCMDLST2WAY;

TRUNCATE TABLE AMRCMDLST2WAY;

INSERT INTO AMRCMDLST2WAY 
SELECT * FROM TEMP_AMRCMDLST2WAY
WHERE AMRCALJOBNO IN (
SELECT AMRCALJOBNO
FROM AMRCALENDAR
WHERE AMRCALCUTOFFDT > SYSDATE);

COMMIT;

DROP TABLE TEMP_AMRCMDLSTMIT;

CREATE TABLE TEMP_AMRCMDLSTMIT AS SELECT * FROM AMRCMDLSTMIT;

TRUNCATE TABLE AMRCMDLSTMIT;

INSERT INTO AMRCMDLSTMIT 
SELECT * FROM TEMP_AMRCMDLSTMIT
WHERE AMRCALJOBNO IN (
SELECT AMRCALJOBNO
FROM AMRCALENDAR
WHERE AMRCALCUTOFFDT > SYSDATE);

COMMIT;

DROP TABLE TEMP_AMRCMDLSTMITDATA;

CREATE TABLE TEMP_AMRCMDLSTMITDATA AS SELECT * FROM AMRCMDLSTMITDATA;

TRUNCATE TABLE AMRCMDLSTMITDATA;

INSERT INTO AMRCMDLSTMITDATA 
SELECT * FROM TEMP_AMRCMDLSTMITDATA
WHERE AMRCALJOBNO IN (
SELECT AMRCALJOBNO
FROM AMRCALENDAR
WHERE AMRCALCUTOFFDT > SYSDATE);

COMMIT;

DROP TABLE TEMP_AMRCMDLSTRETRY;

CREATE TABLE TEMP_AMRCMDLSTRETRY AS SELECT * FROM AMRCMDLSTRETRY;

TRUNCATE TABLE AMRCMDLSTRETRY;

INSERT INTO AMRCMDLSTRETRY
SELECT * FROM TEMP_AMRCMDLSTRETRY
WHERE AMRCALJOBNO IN (
SELECT AMRCALJOBNO
FROM AMRCALENDAR
WHERE AMRCALCUTOFFDT > SYSDATE);

COMMIT;

DROP TABLE TEMP_AMRCMDLST;

CREATE TABLE TEMP_AMRCMDLST AS SELECT * FROM AMRCMDLST;

TRUNCATE TABLE AMRCMDLST;

INSERT INTO AMRCMDLST 
SELECT * FROM TEMP_AMRCMDLST
WHERE AMRCALJOBNO IN (
SELECT AMRCALJOBNO
FROM AMRCALENDAR
WHERE AMRCALCUTOFFDT > SYSDATE);

COMMIT;

UPDATE OUTCMD 
SET STATE = 1
WHERE STATE IN (2,7);

COMMIT;

DROP TABLE TEMP_OUTCMD;

CREATE TABLE TEMP_OUTCMD AS SELECT * FROM OUTCMD;

TRUNCATE TABLE OUTCMD;

INSERT INTO OUTCMD 
SELECT * FROM TEMP_OUTCMD
WHERE STATE IN (1,3,4);

COMMIT;

DROP TABLE TEMP_INCMD;

CREATE TABLE TEMP_INCMD AS SELECT * FROM INCMD;

TRUNCATE TABLE INCMD;

INSERT INTO INCMD 
SELECT * FROM TEMP_INCMD
WHERE MSEXID IN (
SELECT MSEXID
FROM OUTCMD);

COMMIT;

DROP TABLE TEMP_METRSP_INPQ;

CREATE TABLE TEMP_METRSP_INPQ AS SELECT * FROM METRSP_INPQ;

TRUNCATE TABLE METRSP_INPQ;

INSERT INTO METRSP_INPQ
SELECT * FROM TEMP_METRSP_INPQ
WHERE MSEXID IN (
SELECT MSEXID 
FROM INCMD);

COMMIT;

Exit the TNS Watchdog GUI.

Open a Windows Command Prompt.
In the command prompt window, type the following at the prompt

E: <ENTER>
CD db_maint\scripts<ENTER>

Perform export of DCSI user:  exp dcsi/PASSWORD@meter FILE=<name>.dmp LOG=<name>.log

1.  The following step will create a report of the current record count in the tables as well as the results of maintenance for 2 years, 18 months, and 1 year
Sqlplus dcsi/dcsi@meter @db_assessment.sql<ENTER>

2.  This step will list all indexes on the tables on which we will reduce records.  These indexes will be dropped and recreated in a later step.
Sqlplus dcsi/dcsi@meter @collect_indexes.sql <ENTER>

3.  This step will list all triggers on the tables on which we will reduce records.  All triggers will be disabled, and the correct ones will need to be enabled at a later step.
Sqlplus dcsi/dcsi@meter @collect_triggers.sql<ENTER>

4. Execute the following command for the amount of data you would like to keep: 
If you would like to keep 2 years of data:   
Export_data <ENTER>

5.  This step will drop all indexes and disable all triggers, and then remove all records from all tables for which the count of records will be reduced.
Sqlplus dcsi/dcsi@meter @drop_indexes.sql <ENTER>

6.  This step will ensure that no indexes exist on the tables any longer.  IF ANYTHING IS RETURNED BY THIS QUERY, CONTACT DCSI BEFORE PROCEEDING. Any existing indexes will greatly increase the amount of time required for maintenance to complete.
Sqlplus dcsi/dcsi@meter @collect_indexes_int.sql <ENTER>

You can also open SQLPLUS and type the following command for all the indexes returned from the above query:

Drop index <indexname>;
Commit;

The indexname is the name that is returned on the right hand column of the results from the above command in step 6.

7.  The following step will place the correct amount of data back into the tables.
Import_data <ENTER>

8.  The following command will recreate the indexes and enable ALL triggers on the tables.  It will then create a report of the status of all triggers on the tables.
Sqlplus dcsi/dcsi@meter @add_indexes.sql <ENTER>

9. You must now disable/enable and verify all indexes/triggers which were enabled/disabled before maintenance began.
Sqlplus dcsi/dcsi@meter @collect_triggers2.sql<ENTER>
Sqlplus dcsi/dcsi@meter @collect_indexes2.sql<ENTER>

If an error is returned stating that amrhourlydata and meteracctsmit primary keys had a problem being created you will need to login to Enterprise Manager for oracle 9i or Database Console for oracle 10G and enable these 2 primary keys.  Please call Aclara if you need help with this step.  More detailed instructions will follow shortly.  Make sure you enable these before you continue and start the services.

10.  This step will rebuild all indexes on the table to ensure efficiency.  You can run this step and then immediately start the services in watchdog while it runs.
Sqlplus dcsi/dcsi@meter @cleansystem.sql <ENTER>

Open the TNS Watchdog GUI
Start all services.
Exit the TNS Watchdog GUI (save the settings).

Additional Items to check:
-  How much hard drive space is being used by Oracle Archive logs
	- Archive logs SHOULD be located in the following directories
		- Oracle 9i or Previous -  E:\oracle\oradata\DCSI\Archive
		- Oracle 10G – E:\oracle\product\10.2.0\db_1\oradata\Archive
-  How much hard drive space is being utilized by Bill Files
	- Bill files are located in F:\users\CUSTOMER DIRECTORY\AMR
-  How much hard drive space is being utilized by TNS Transaction log files
	- located in F:\users\defaults
-  The number of Job Numbers available before the calendar rolls over and begins duplicating Job Numbers in the calendar and/or Meter read history – to check the amrcalendar job numbers yourself you can run the following commands in SQLPLUS:

Select min(amrcaljobno) from amrcalendar; - will show you the minimum job number
Select max(amrcaljobno) from amrcalendar; - will show you the maximum job number
Select seq_amrcaljobno.nextval from dual; - will show you the next available job number

To determine the exact amount of time before you will hit an old job number you can spool all the job numbers to a file called c:jobno.txt and check the results:

In SQLPLUS:

Set head off;
Set pages 0;
Set lines 150;
Spool c:\jobno.txt;
Select amrcaljobno, amrrun, amrcalstartdt, amrcalstopdt, amrcalcutoffdt from amrcalendar order by amrcaljobno;
Spool off;

Delete from amrcalendar where amrrun = ‘DONE’ and amrcalstartdt < sysdate – 60;
Delete rows from AMRCALENDAR
AMRJOBDATA
AMRJOBSUMMARY
AMRSECONDSETID
METERACCTSTC
METERACCTSMIT
METERACCTSDEMAND
METERINGQCSTATUS
tables based on what job numbers are left in the amrcalendar table.
You can also contact Aclara to remove other job numbers from the amrcalendar table base on amrcalcutoffdt that is in the past and does not exist in the history tables.

Database Maintenance is complete and your meter history is down to 90 days of reads.
"""    

    
                                            

